﻿using System;
using System.Data;
using System.Data.ProviderBase;
using System.Data.Sql;
using System.Data.SqlClient;

using System.Text;
using System.Collections;
using System.Collections.Specialized;
using System.Collections.Generic;

namespace tab2sql
{
    /// <summary>
    /// generic db layer for sql server connections
    /// November 2008
    /// </summary>
    public class db
    {
        /// <summary>
        /// Constructor.
        /// </summary>
        public db()
        {
    
                ConnectionString = DefaultConnnectionString;
         }

        /// <summary>
        /// Constructor if you want a different connection string.
        /// </summary>
        /// <param name="iString"></param>
        public db(string iString)
        {
            if (iString.Length > 4)
            {
       
                ConnectionString = iString;
            }
            else
            {
           
                ConnectionString = DefaultConnnectionString;
            }
        }

        /// <summary>
        /// Current connection string.
        /// </summary>
        public string ConnectionString = "";
        /// <summary>
        /// Saved Connection String.
        /// </summary>
        const string DefaultConnnectionString = @"Data Source=\\.\pipe\SQLLocal\WTF;Application Name=DSql;Initial Catalog=tab2SQL;Integrated Security=True";
        /// <summary>
        /// Where any error message is saved.
        /// </summary>
        public string ErrorMsg = "";
        /// <summary>
        /// Record of rows returned in last statement.
        /// </summary>
        public long RowCount = 0;
        /// <summary>
        /// Default catalog.
        /// </summary>
        public string DatabaseCatalog = "";

        /// <summary>
        /// Time in milliseconds last query took to execute.
        /// </summary>
        public long QueryTimeMS = 0;
        /// <summary>
        /// Server or previous query.
        /// </summary>
        public string ServerID = "";
        /// <summary>
        /// Time class was initialised. Value in ticks. 
        /// </summary>
        public double TimeA = getTime();
        /// <summary>
        /// Time query reached the second stage. Not in use.
        /// </summary>
        public double TimeB = 0;
        /// <summary>
        /// Time query reached the third stage. Not in use.
        /// </summary>
        public double TimeC = 0;

        /// <summary>
        /// executes Sql statement, returns a row count. For updates, deletes, etc. 
        /// </summary>
        /// <param name="cmdX"></param>
        /// <returns></returns>
        public int ExecuteSql(SqlCommand cmdX)
        {
            
            int rowCount = 0;

            using (SqlConnection conn = new SqlConnection(GetConnString()))
            {
                conn.Open();
                cmdX.Connection = conn;
                try
                {
                    rowCount = cmdX.ExecuteNonQuery();
                    RowCount = rowCount;
                    ConnectionString = cmdX.Connection.ConnectionString.ToString();
                    DatabaseCatalog = conn.Database.ToString();
                    ServerID = conn.ServerVersion.ToString();
                }
                catch (Exception ex)
                {
                    ErrorMsg += ex.Message.ToString();
                    rowCount = -1;
                }

                return rowCount;
            }
        }
        /// <summary>
        /// executes sql, gives rowcount.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int ExecuteSql(string SqlString)
        {
            TimeB = getTime();
            int rowCount = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = SqlString;

            rowCount = ExecuteSql(cmd);
            RowCount = rowCount;
            TimeC = getTime();
            return rowCount;
        }

        /// <summary>
        /// returns a connection string.
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetConnected()
        {
            SqlConnection myConn = new SqlConnection(GetConnString());
            return myConn;
        }

        /// <summary>
        /// returns datatable from sql query command.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString)
        {
            TimeB = getTime();
            DataTable dt = new DataTable();
            try
            {

                using (SqlConnection conn = new SqlConnection(GetConnString()))
                {
                    using (SqlCommand cmd = new SqlCommand(SqlString, conn))
                    {
                        conn.Open();
                        IDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        RowCount = dt.Rows.Count;
                        ConnectionString = conn.ConnectionString.ToString();
                        DatabaseCatalog = conn.Database.ToString();
                       
                        conn.Close();
                        TimeC = getTime();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            { this.ErrorMsg += ex.Message; }
            return dt;
        }
        public DataTable RenderSQL(string SqlString, Dictionary<string, string> args)
        {
            return RenderSQL(SqlString, convertArgs(args));
        }

        public DataTable RenderSQL(string SqlString, SqlParameter[] param)
        {
            TimeB = getTime();
            DataTable dt = new DataTable();
            try
            {

                using (SqlConnection conn = new SqlConnection(GetConnString()))
                {
                    using (SqlCommand cmd = new SqlCommand(SqlString, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        foreach (SqlParameter p in param)
                        {
                            cmd.Parameters.Add(p);
                        }
                        conn.Open();
                        IDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        RowCount = dt.Rows.Count;

                        ConnectionString = conn.ConnectionString.ToString();
                        DatabaseCatalog = conn.Database.ToString();
                        DGen.DFiles.WriteToLog(RowCount.ToString() + " row(s)");
                        DGen.DFiles.WriteToLog(cmd.Parameters.Count.ToString() + " param(s)");
                        DGen.DFiles.WriteToLog(cmd.Parameters[0].ParameterName);
                        DGen.DFiles.WriteToLog(cmd.Parameters[0].Value.ToString());
                        conn.Close();
                        TimeC = getTime();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            { this.ErrorMsg += ex.Message; }
            
            return dt;
        }



        private SqlParameter[] convertArgs(Dictionary<string, string> inArgs)
        {
            SqlParameter[] outArgs = new SqlParameter[inArgs.Count];
            int i = 0;
            foreach (KeyValuePair<string,string> dictE in inArgs)
            {
                outArgs[i] = new SqlParameter(dictE.Key, dictE.Value);
                i++;
            }

            return outArgs;

        }

   

        private static double getTime()
        {
            double epoch = (double)(DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalMilliseconds;
            return epoch;

        }
   


        public SqlDataAdapter GetAdapter(string SqlString)
        {
            SqlCommand cmd = new SqlCommand(SqlString);
            SqlConnection conn = new SqlConnection(GetConnString());
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            return da;

        }
        
     


        private string GetConnString()
        {
            return this.ConnectionString;
        }
      




    }
}
